﻿using System;
using System.Configuration;
using System.Data;

//using CoreLab.Oracle; //using Devart.Data.Oracle; //using Oracle.DataAccess.Client; 
////using Oracle.DataAccess.Types;

using CoreLab.Oracle; //using Devart.Data.Oracle;

namespace LMD.Lib.Oracle
{
    public class DBLib
    {
        private String m_sxConnectionString;
        private OracleConnection m_conn;
        private OracleTransaction m_trx;
        private OracleCommand m_cmd;

        public DBLib()
        {
            m_sxConnectionString = Connection.ICP;
        }

        public DBLib(String sxConnectionString)
        {
            m_sxConnectionString = sxConnectionString;
        }

        public String ConnectionString
        {
            get { return m_sxConnectionString; }
            set { m_sxConnectionString = value; }
        }

        public OracleCommand GetCommand()
        {
            if( m_cmd == null )
                m_cmd = m_conn.CreateCommand();

            return m_cmd;
        }

        public OracleConnection OpenConnection()
        {
            m_conn = new OracleConnection(m_sxConnectionString);
            m_conn.Open();
            return m_conn;
        }

        public void CloseConnection()
        {
            if( m_conn != null )
                m_conn.Close();

            m_conn = null;
            m_cmd = null;
        }

        public OracleTransaction BeginTransaction()
        {
            if( m_conn == null )
                OpenConnection();

            m_trx = m_conn.BeginTransaction();
            return m_trx;
        }

        public void EndTransaction(bool bDoCommit)
        {
            if (bDoCommit)
            {
                m_trx.Commit();
            }
            else
            {
                m_trx.Rollback();
            }
            
            m_trx = null;

            CloseConnection();
        }

        /// <summary>
        /// Fungsi untuk menjalankan perintah query
        /// </summary>
        /// <param name="sxQuery">isi query</param>
        /// <returns>DataTable</returns>
        /// <versioning>
        /// 1 24-Nov-2011 1.0 Rikaro
        /// </versioning>

        public DataTable GetDataTable(String sxQuery)
        {
            DataTable dt = new DataTable();

            try
            {
                if (m_conn == null)
                    OpenConnection();

                if (m_cmd == null)
                    m_cmd = m_conn.CreateCommand();

                m_cmd.CommandText = sxQuery;

                if (m_trx != null)
                {
                    m_cmd.Transaction = m_trx;
                }

                dt.Load(m_cmd.ExecuteReader());

                if (m_trx == null)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                string strError = ex.Message.ToString();
            }
            return dt;
        }

        public DataTable GetDataTable(String sxQuery, params OracleParameter[] prmParams)
        {
            DataTable dt = new DataTable();

            try
            {
                if (m_conn == null)
                    OpenConnection();

                if (m_cmd == null)
                    m_cmd = m_conn.CreateCommand();

                m_cmd.CommandText = sxQuery;
                m_cmd.Parameters.Clear();
                m_cmd.Parameters.AddRange(prmParams);
                //m_cmd.PassParametersByName = true;

                if (m_trx != null)
                {
                    m_cmd.Transaction = m_trx;
                }

                dt.Load(m_cmd.ExecuteReader());

                if (m_trx == null)
                {
                    CloseConnection();
                }
            }
            catch (Exception ex)
            {
                string strError = ex.Message.ToString();
            }
            return dt;
        }

        public object GetSingleValue(String sxQuery, params OracleParameter[] prmParams)
        {
            if (m_conn == null)
                OpenConnection();

            if (m_cmd == null)
                m_cmd = m_conn.CreateCommand();

            m_cmd.CommandText = sxQuery;
            m_cmd.Parameters.Clear();
            m_cmd.Parameters.AddRange(prmParams);
            //m_cmd.PassParametersByName = true;

            if (m_trx != null)
            {
                m_cmd.Transaction = m_trx;
            }

            object objReturn  = m_cmd.ExecuteScalar();

            if (m_trx == null)
            {
                CloseConnection();
            }

            return objReturn;
        }

        public object[] GetMultipleValues(String sxQuery, params OracleParameter[] prmParams)
        {
            if (m_conn == null)
                OpenConnection();

            if (m_cmd == null)
                m_cmd = m_conn.CreateCommand();

            m_cmd.CommandText = sxQuery;
            m_cmd.Parameters.Clear();
            m_cmd.Parameters.AddRange(prmParams);
            //m_cmd.PassParametersByName = true;

            if (m_trx != null)
            {
                m_cmd.Transaction = m_trx;
            }

            OracleDataReader dr = m_cmd.ExecuteReader();            

            if (dr.Read())
            {
                object[] arrObject = new object[dr.FieldCount];


                for (int i = 0; i < dr.FieldCount; i++)
                {
                    arrObject[i] = dr[i];
                }

                if (m_trx == null)
                {
                    CloseConnection();
                }

                return arrObject;
            }
            else
            {
                if (m_trx == null)
                {
                    CloseConnection();
                }

                return null;
            }
        }

        public int ExecuteNonQuery(String sxQuery, params OracleParameter[] prmParams)
        {
            if (m_conn == null)
                OpenConnection();

            if (m_cmd == null)
                m_cmd = m_conn.CreateCommand();

            m_cmd.CommandText = sxQuery;

            m_cmd.Parameters.Clear();
            m_cmd.Parameters.AddRange(prmParams);
            //m_cmd.PassParametersByName = true;

            if (m_trx != null)
            {
                m_cmd.Transaction = m_trx;
            }

            int iReturn = m_cmd.ExecuteNonQuery();

            if (m_trx == null)
            {
                CloseConnection();
            }

            return iReturn;
        }

        public int ExecuteProcedure(String sxProcedureName, params OracleParameter[] prmParams)
        {
            if (m_conn == null)
                OpenConnection();

            if (m_cmd == null)
                m_cmd = m_conn.CreateCommand();

            m_cmd.CommandType = CommandType.StoredProcedure;
            m_cmd.CommandText = sxProcedureName;

            m_cmd.Parameters.Clear();
            m_cmd.Parameters.AddRange(prmParams);
            //m_cmd.PassParametersByName = true;

            if (m_trx != null)
            {
                m_cmd.Transaction = m_trx;
            }

            int iReturn = m_cmd.ExecuteNonQuery();

            if (m_trx == null)
            {
                CloseConnection();
            }

            return iReturn;
        }        
    }
}
